﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Text;
using System.Transactions;
using System.Web;
using Eacademy.Domain.Abstractions;
using Eacademy.Domain.Entities;
using Eacademy.Domain.Models;
using Eacademy.Library.Helpers;
using Elmah;
using Microsoft.Practices.EnterpriseLibrary.Data;
using SubSonic.DataProviders;

namespace Eacademy.Domain.Repository
{
    public class LessonRepository : ILessonRepository
    {
        EacademyDB db = new EacademyDB();

        public Boolean AddLesson(AdminLessonsViewModel adminLessonsViewModel, int subjectId,int mentorUserId)
        {
            try
            {

                using (TransactionScope ts = new TransactionScope())
                {
                    using (SharedDbConnectionScope scs = new SharedDbConnectionScope())
                    {
                        Mentor mentor = db.Select.From<Mentor>()
                            .Where(MentorsTable.UserIdColumn).IsEqualTo(mentorUserId)
                            .ExecuteSingle<Mentor>();
                        
                        Lesson lesson = new Lesson();
                        lesson.Title = adminLessonsViewModel.Title;
                        lesson.Lesson = HttpUtility.HtmlDecode(adminLessonsViewModel.Lesson);
                        lesson.Files = adminLessonsViewModel.Files;
                        //lesson.DatePosted = DateTime.Now;
                        lesson.Save();

                        LessonsSubject lessonSubjects = new LessonsSubject();
                        lessonSubjects.SubjectId = subjectId;
                        lessonSubjects.LessonId = lesson.LessonId;
                        lessonSubjects.MentorId = mentor.MentorId;

                        lessonSubjects.Save();


                    }
                    ts.Complete();
                }

                return true;
            }
            catch (Exception ex)
            {
            }
            return false;
        }

        public Boolean UpdateLesson(AdminLessonsViewModel adminLessonsViewModel)
        {
            try
            {

                using (TransactionScope ts = new TransactionScope())
                {
                    using (SharedDbConnectionScope scs = new SharedDbConnectionScope())
                    {
                        Lesson lesson = db.Select.From<Lesson>().Where(LessonsTable.LessonIdColumn)
                            .IsEqualTo(adminLessonsViewModel.LessonId).ExecuteSingle<Lesson>();
                        lesson.Title = adminLessonsViewModel.Title;
                        lesson.Lesson = HttpUtility.HtmlDecode(adminLessonsViewModel.Lesson);
                        lesson.Files = adminLessonsViewModel.Files;
                        //lesson.DateModified = DateTime.Now;
                        lesson.Update();

                    }
                    ts.Complete();
                }

                return true;
            }
            catch (Exception ex)
            {
            }
            return false;

        }

        public Boolean DeleteLesson(int lessonId)
        {
            try
            {

                using (TransactionScope ts = new TransactionScope())
                {
                    using (SharedDbConnectionScope scs = new SharedDbConnectionScope())
                    {
                        LessonBase.Delete(x => x.LessonId == lessonId);
                        LessonsSubjectBase.Delete(x => x.LessonId == lessonId);
                    }
                    ts.Complete();
                }

                return true;
            }
            catch (Exception ex)
            {
            }
            return false;

        }

        public AdminLessonsViewModel GetLessonByLessonId(int id)
        {
            AdminLessonsViewModel adminLessonsViewModel = new AdminLessonsViewModel(); 
            try
            {
                Lesson lesson = db.Select.From<Lesson>().Where(LessonsTable.LessonIdColumn)
                    .IsEqualTo(id).ExecuteSingle<Lesson>();

                adminLessonsViewModel.Title = lesson.Title;
                adminLessonsViewModel.Lesson = lesson.Lesson;
                adminLessonsViewModel.LessonId = id;
                adminLessonsViewModel.Files = lesson.Files;

                LessonsSubject lessonSubjects = db.Select.From<LessonsSubject>().Where(LessonsSubjectsTable.LessonIdColumn)
                    .IsEqualTo(id).ExecuteSingle<LessonsSubject>();

                adminLessonsViewModel.SubjectId = lessonSubjects.SubjectId ?? 0;

                Mentor mentor = db.Select.From<Mentor>().Where(MentorsTable.MentorIdColumn)
                    .IsEqualTo(lessonSubjects.MentorId).ExecuteSingle<Mentor>();


                adminLessonsViewModel.MentorUserId = mentor.UserId ?? 0;

                return adminLessonsViewModel;
            }
            catch (Exception ex)
            {
                ErrorSignal.FromCurrentContext().Raise(ex);
            }
            return adminLessonsViewModel;
        }

        public List<AdminLessonsViewModel> GetLessonsBySubjectId(int subjectId,int mentorUserId,int pageIndex, int pageSize, out int totalCount, bool count = true)
        {
            totalCount = 0;
            DbFactory factory = new DbFactory();
            Database db = factory.database;
            List<AdminLessonsViewModel> lessonViewList = new List<AdminLessonsViewModel>();
            try
            {
                StringBuilder sql = new StringBuilder();
                sql.AddStartRemove();
                sql.AppendLine(@"SELECT l.*,ls.subject_id");
                sql.AddEndRemove();
                sql.AppendLine(@"FROM lessons_subjects ls");
                sql.AppendLine(@"LEFT JOIN lessons l ON ls.lesson_id = l.lesson_id ");
                sql.Append(" WHERE 1 = 1 ");
                sql.Append(" AND ls.subject_id = ?SubjectId ");
                sql.AppendLine(" AND ls.mentor_id = (SELECT m.mentor_id FROM mentors m WHERE m.user_id = ?MentorUserId) ");

                if (pageSize > 0)
                {
                    //control page size
                    pageSize = pageSize > 10 ? 10 : pageSize;

                    sql.AddStartRemove();
                    sql.Append(" LIMIT ?StartIndex, ?MaxRecord ");
                    sql.AddEndRemove();
                }

                DbCommand comm = db.GetSqlStringCommand(sql.ToString());
                db.AddInParameter(comm, "?SubjectId", DbType.Int32, subjectId);
                db.AddInParameter(comm, "?StartIndex", DbType.Int32, (pageIndex - 1) * pageSize);
                db.AddInParameter(comm, "?MaxRecord", DbType.Int32, pageSize);
                db.AddInParameter(comm, "?MentorUserId", DbType.Int32, mentorUserId);
                DataSet ds = factory.ExecuteQuery(comm);

                DataTable studentsTable = ds.Tables[0];


                lessonViewList = studentsTable.AsEnumerable().Select(datarow => new AdminLessonsViewModel
                {
                    Lesson = datarow["lesson"].ToString(),
                    LessonId = datarow["lesson_id"].ToInteger(),
                    SubjectId = datarow["subject_id"].ToInteger(),
                    Title = datarow["title"].ToString()
                }).ToList();

                if (count)
                {
                    string countQuery = sql.ToString().GenerateCountQuery();
                    comm.CommandText = countQuery;
                    ds = factory.ExecuteQuery(comm);
                    totalCount = ds.Tables[0].Rows[0]["count"].ToInteger();
                }


                return lessonViewList;
            }
            catch (Exception ex)
            { }
            return lessonViewList;
        }
    }
}